package com.stores.v1.model;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;

import com.stores.v1.entity.Address;
import com.stores.v1.entity.Person;
import com.stores.v1.entity.Phone;
import com.stores.v1.entity.UserAccount;
import com.stores.v1.toolset.DatabaseInitializationException;

public abstract class PersonModel extends BaseModel {
	private Person userContext;

	public Person getUserContext() {
		return userContext;
	}

	public void setUserContext(Person userContext) {
		this.userContext = userContext;
	}

	/***
	 * 
	 * @param user
	 * @return
	 * @throws AccessControlException
	 * @throws DatabaseTransactionException
	 * @throws DatabaseInitializationException
	 * @throws DatabaseCommandException
	 */
	public abstract int createUser(Person user)
			throws DatabaseInitializationException,
			DatabaseTransactionException, AccessControlException,
			DatabaseCommandException;

	/***
	 * 
	 * @param user
	 * @return
	 * @throws DatabaseInitializationException
	 * @throws DatabaseTransactionException
	 */
	public int deleteUser(Person user) throws DatabaseInitializationException,
			DatabaseTransactionException {
		Connection connection = this.getDatabase().getOpenConnection();
		int rowsAffected = 0;
		try {
			connection.setAutoCommit(false);// start transaction
			StringBuilder sql = new StringBuilder();
			PreparedStatement command = connection.prepareStatement(sql
					.toString());
			command.addBatch(String.format(
					"delete from usergroups where personId=%d;", user.getId()));
			command.addBatch(String.format(
					"delete from userAccount where personId=%d;", user.getId()));
			command.addBatch(String
					.format("delete from address where addressId in (select addressId from person where personId=%d);",
							user.getId()));
			command.addBatch(String
					.format("delete from phone where phoneId in (select phoneId from person where personId=%d);",
							user.getId()));
			command.addBatch(String.format(
					"delete from person where personId=%d;", user.getId()));
			command.executeBatch();
			// this.addToActivityLog(this.getUserContext().getId(), this
			// .getAccessControl().getPrivilegeId(), connection);
			connection.commit();
			this.getDatabase().closeConnection(connection);
		} catch (Exception ex) {
			try {
				connection.rollback();
				this.getDatabase().closeConnection(connection);
				throw new DatabaseTransactionException(String.format(
						"Could not delete user due to [%s]", ex.getMessage()));
			} catch (SQLException e) {
				throw new DatabaseTransactionException(String.format(
						"Could not delete user due to [%s]", e.getMessage()));
			}
		}
		return rowsAffected;
	}

	/***
	 * 
	 * @param person
	 * @param groupId
	 * @return
	 * @throws DatabaseInitializationException
	 * @throws DatabaseTransactionException
	 */
	protected int createPerson(Person person, int groupId)
			throws DatabaseInitializationException,
			DatabaseTransactionException {
		Connection connection = this.getDatabase().getOpenConnection();
		int personId = 0;
		try {
			connection.setAutoCommit(false);// start transaction
			int addressId = this.createAddress(person.getAddress(), connection);
			int phoneId = this.createPhone(person.getPhone(), connection);
			Address address = person.getAddress();
			address.setId(addressId);
			person.setAddress(address);
			Phone phone = person.getPhone();
			phone.setId(phoneId);
			person.setPhone(phone);
			personId = this.createPerson(person, connection);
			person.setId(personId);
			UserAccount account = person.getAccount();
			account.setPersonId(personId);
			person.setAccount(account);
			int userAccountId = this.createUserAccount(person.getAccount(),
					connection);
			account.setId(userAccountId);
			this.addToSecurityGroup(person, groupId, connection);
			// this.addToActivityLog(this.userContext.getId(), this
			// .getAccessControl().getPrivilegeId(), connection);
			connection.commit();
			this.getDatabase().closeConnection(connection);
		} catch (Exception ex) {
			try {
				connection.rollback();
				this.getDatabase().closeConnection(connection);
				throw new DatabaseTransactionException(String.format(
						"User creation failed due to [%s]", ex.getMessage()));
			} catch (SQLException e) {
				throw new DatabaseTransactionException(String.format(
						"User creation failed due to [%s]", e.getMessage()));
			}
		}
		return personId;
	}

	/**
	 * 
	 * @param person
	 * @param groupId
	 * @param connection
	 * @throws DatabaseCommandException
	 */
	private void addToSecurityGroup(Person person, int groupId,
			Connection connection) throws DatabaseCommandException {
		StringBuilder sql = new StringBuilder();
		sql.append("insert into usergroups(groupId,personId) values(?,?);");
		try {
			PreparedStatement command = connection.prepareStatement(sql
					.toString());
			command.setInt(1, groupId);
			command.setInt(2, person.getId());
			command.executeUpdate();
		} catch (SQLException e) {
			throw new DatabaseCommandException(String.format(
					"Failed to add user [%d] to security group due to [%s]",
					person.getId(), e.getMessage()));
		}
	}

	/***
	 * 
	 * @param account
	 * @param connection
	 * @return
	 * @throws DatabaseCommandException
	 */
	private int createUserAccount(UserAccount account, Connection connection)
			throws DatabaseCommandException {
		StringBuilder sql = new StringBuilder();
		sql.append("insert into userAccount(personId,pwd,dateCreated) values(?,?,NOW());");
		try {
			PreparedStatement command = connection.prepareStatement(
					sql.toString(), Statement.RETURN_GENERATED_KEYS);
			command.setInt(1, account.getPersonId());
			command.setString(2, account.getPassword());
			command.executeUpdate();
			return this.getGeneratedKey(command.getGeneratedKeys());
		} catch (SQLException e) {
			throw new DatabaseCommandException(
					String.format("Failed to create user account due to [%s]",
							e.getMessage()));
		}
	}

	/***
	 * 
	 * @param person
	 * @param groupId
	 * @param connection
	 * @return
	 * @throws DatabaseCommandException
	 */
	private int createPerson(Person person, Connection connection)
			throws DatabaseCommandException {
		StringBuilder sql = new StringBuilder();
		sql.append("insert into person(firstName,lastName,emailAddress,addressId,phoneId) values(?,?,?,?,?);");
		try {
			PreparedStatement command = connection.prepareStatement(
					sql.toString(), Statement.RETURN_GENERATED_KEYS);
			command.setString(1, person.getFirstName());
			command.setString(2, person.getLastName());
			command.setString(3, person.getEmailAddress());
			command.setInt(4, person.getAddress().getId());
			command.setInt(5, person.getPhone().getId());
			command.executeUpdate();
			return this.getGeneratedKey(command.getGeneratedKeys());
		} catch (SQLException e) {
			throw new DatabaseCommandException(String.format(
					"Failed to create person due to [%s]", e.getMessage()));
		}
	}

	/**
	 * 
	 * @param phone
	 * @param connection
	 * @return
	 * @throws DatabaseCommandException
	 */
	private int createPhone(Phone phone, Connection connection)
			throws DatabaseCommandException {
		StringBuilder sql = new StringBuilder();
		sql.append("insert into phone(areaCode,number) values(?,?);");
		try {
			PreparedStatement command = connection.prepareStatement(
					sql.toString(), Statement.RETURN_GENERATED_KEYS);
			command.setString(1, phone.getAreaCode());
			command.setString(2, phone.getNumber());
			command.executeUpdate();
			return this.getGeneratedKey(command.getGeneratedKeys());
		} catch (SQLException e) {
			throw new DatabaseCommandException(String.format(
					"Failed to create phone due to [%s]", e.getMessage()));
		}
	}

	/***
	 * 
	 * @param address
	 * @param connection
	 * @return
	 * @throws DatabaseCommandException
	 */
	private int createAddress(Address address, Connection connection)
			throws DatabaseCommandException {
		StringBuilder sql = new StringBuilder();
		sql.append("insert into address(house,street,locality,line1,city,state) values(?,?,?,?,?,?);");
		try {
			PreparedStatement command = connection.prepareStatement(
					sql.toString(), Statement.RETURN_GENERATED_KEYS);
			command.setString(1, address.getHouse());
			command.setString(2, address.getStreet());
			command.setString(3, address.getLocality());
			command.setString(4, address.getLine1());
			command.setString(5, address.getCity());
			command.setString(6, address.getState());
			command.executeUpdate();
			return this.getGeneratedKey(command.getGeneratedKeys());
		} catch (SQLException e) {
			throw new DatabaseCommandException(String.format(
					"Failed to create address due to [%s]", e.getMessage()));
		}
	}

	/***
	 * 
	 * @return
	 * @throws DatabaseInitializationException
	 * @throws DatabaseCommandException
	 */
	protected boolean isAllowed() throws DatabaseInitializationException,
			DatabaseCommandException {
		this.getAccessControl().setDatabase(this.getDatabase());
		this.getAccessControl().setUserContext(this.getUserContext());
		boolean isAllowed = this.getAccessControl().isAllowed();
		return isAllowed;
	}
}
